package main;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

public class MyExecute {
	private static final Logger log = Logger.getLogger(MyExecute.class);

	private Statement stmt;
	private Connection localConnection;
	private Connection targetConnection;

	private static final String DIR_OF_SQL = "/sqlScript/";
	private static final String INS_TABLE = "SESSION_TRACKING";
	private static final String[] REPLACE_STATEMENTS = { "${TABLE}", "${COLUMN}", "$(VALUES)" };

	public MyExecute() {
		log.info("Initialize " + getClass().getCanonicalName());
		this.doConnectToLocal();
	}

	private boolean doConnectToLocal() {
		try {
			localConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "MySchema", "oracle");
		}catch (SQLException e) {
			log.error(e.getMessage(), e.getCause());
		}

		if(localConnection == null) {
			return false;
		}

		return true;
	}

	public boolean doConnectTarget() {
		try {
			targetConnection = DriverManager.getConnection("");
		}catch (SQLException e) {
			log.error(e.getMessage(), e.getCause());
		}

		if(targetConnection == null) {
			return false;
		}

		return true;
	}

	public void execute(Connection connection) {
		if(connection == null) {
			log.info("There is no connection: " + connection);
			return;
		}
	}

	public void myExecuteSQL(Connection connection, String st1, String st2) {
		if(connection == null) {
			log.info("There is no connection: " + connection);
			return;
		}

		StringBuilder sb = new StringBuilder();
		List<String> replaceValues = new ArrayList<>(REPLACE_STATEMENTS.length);
		replaceValues.add(INS_TABLE); // TABLE NAME

		try {
			/* Get statement for querying data from target */
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(getSQLStr(st1));
			ResultSetMetaData rsmd = rs.getMetaData();

			/* Get and prepare list of columns */
			int numberOfColumns = rsmd.getColumnCount();
			for(int i = 1; i <= numberOfColumns; i++) {
				if(i > 1)
					sb.append(", ");
				sb.append(rsmd.getColumnName(i));
			}
			sb.append(", SQL_TEXT");
			replaceValues.add(sb.toString()); // COLUMNS
			sb.delete(0, sb.length()); // clear temple

			/* Get and prepare list of values */
			while (rs.next()) {
				for(int i = 1; i <= numberOfColumns; i++) {
					if(i > 1)
						sb.append(", ");
					if(i == numberOfColumns) {
						sb.append("systimestamp");
						break;
					}

					sb.append("'" + rs.getString(i) + "'");
				}

				// Get sql text
				String statement2 = getSQLStr(st2);
				statement2 = statement2.replace("${SID}", rs.getString("SID"));
				ResultSet rs2 = stmt.executeQuery(statement2);
				if(rs.next())
					sb.append(", " + "'" + rs2.getString("SQL_TEXT") + "'");
				else
					sb.append(", ''");
				rs2.close(); // close rs2

				replaceValues.add(sb.toString()); // VALUES
				sb.delete(0, sb.length()); // clear temple

				/* Create the insert statement from insert template */
				/* Execute insert statement to insert data to local connection */
				doInsertToLocalDB(createInsertStatement(replaceValues));
			}

			rs.close(); // close rs1
			stmt.close(); // close statement
		}catch (SQLException e) {
			log.error(e.getMessage(), e.getCause());
		}
	}

	/**
	 * Create the insert statement from insert template
	 * 
	 * @param replaceValues
	 * @return
	 */
	private String createInsertStatement(List<String> replaceValues) {
		StringBuilder inStm = new StringBuilder();
		final String insTmp = "insertTemplate.sql";
		InputStream is = getClass().getResourceAsStream(DIR_OF_SQL + insTmp);
		BufferedReader br = new BufferedReader(new InputStreamReader(is));
		String line = null;
		try {
			while ((line = br.readLine()) != null) {
				int size = REPLACE_STATEMENTS.length;
				for(int i = 0; i < size; i++) {
					if(line.contains(REPLACE_STATEMENTS[i])) {
						line = line.replace(REPLACE_STATEMENTS[i], replaceValues.get(i));
					}
				}
				inStm.append(line + "\n");
			}
			br.close(); // close reading file
			is.close();
		}catch (IOException e) {
			log.error(e.getMessage(), e.getCause());
		}

		/* Remove the ; at last statement if has */
		int i = -1;
		while ((i = inStm.indexOf(";")) != -1) {
			inStm.deleteCharAt(i);
		}

		/* Remove third values for renewing */
		if(replaceValues.size() > 2)
			replaceValues.remove(2);
		return inStm.toString();
	}

	/**
	 * Execute insert statement to insert data to local connection
	 * 
	 * @param inStm
	 */
	private void doInsertToLocalDB(String insStm) {
		if(insStm != null) {
			log.info(insStm);

			Statement statement = null;
			try {
				statement = getLocalConnection().createStatement(); // Insert to local database
				statement.executeUpdate(insStm);
				statement.execute("COMMIT");
				statement.close();
			}catch (SQLException e) {
				try {
					if(statement != null && !statement.isClosed()) {
						statement.execute("ROLLBACK");
						statement.close();
					}
				}catch (SQLException e1) {
					log.error(e1.getMessage(), e1.getCause());
				}
				log.error(e.getMessage(), e.getCause());
			}

		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#finalize()
	 */
	@Override
	protected void finalize() throws Throwable {
		log.info("Application off");
		if(stmt != null && !stmt.isClosed()) {
			stmt.close();
		}

		if(this.localConnection != null) {
			this.localConnection.close();
		}

		if(this.targetConnection != null) {
			this.targetConnection.close();
		}

		super.finalize();
	}

	/**
	 * @return the localConnection
	 */
	public Connection getLocalConnection() {
		return localConnection;
	}

	/**
	 * @return the targetConnection
	 */
	public Connection getTargetConnection() {
		return targetConnection;
	}

	public String getSQLStr(String fileName) {
		StringBuilder sb = new StringBuilder();

		InputStream is = getClass().getResourceAsStream(DIR_OF_SQL + fileName);
		BufferedReader br = new BufferedReader(new InputStreamReader(is));
		try {
			String bfStr = null;
			while ((bfStr = br.readLine()) != null) {
				sb.append(bfStr + "\n");
			}
		}catch (IOException e) {
			log.error(e.getMessage(), e.getCause());
		}

		/* Remove the ; at last statement if has */
		int idxOfc = -1;
		while ((idxOfc = sb.indexOf(";")) != -1) {
			sb.deleteCharAt(idxOfc);
		}

		return sb.toString();
	}
}

class PrintColumnTypes {

	public static void printColTypes(ResultSetMetaData rsmd, StringBuilder sb) throws SQLException {
		int columns = rsmd.getColumnCount();
		for(int i = 1; i <= columns; i++) {
			int jdbcType = rsmd.getColumnType(i);
			String name = rsmd.getColumnTypeName(i);
			sb.append("Column " + i + " is JDBC type " + jdbcType);
			sb.append(", which the DBMS calls " + name);
			sb.append('\n');
		}
	}
}
